from flask import jsonify
from app.api import handle
from app.setting import DevConfig
import os
import pandas as pd
import traceback

from app.needFields import StudentData,need_cloumns,characterData,achievementData,bookData

CURRENT = os.path.dirname(os.path.dirname(__file__))
UPLOAD_PATH = CURRENT + '/api/updata/'
HANDLE_PATH = CURRENT + '/api/handle/'

engine=DevConfig.engine

#学生数据字段
student_data=StudentData
student_key=list(StudentData.keys())
need_cloumns=need_cloumns


#性格测试数据
charact_data=characterData
charact_key=list(characterData.keys())

#成绩表
achievement_data=achievementData
achievement_key=list(achievementData.keys())


book_data=bookData
book_key=list(bookData.keys())



@handle.route('/handleFile/studentData',methods=['POST'])
def parse_studentdata(datapath):
    try:
        print("开始解析",datapath)
        df = pd.read_excel(datapath)
        df['全国各省'] = None
        df['广东各市'] = None
        for i in df.index:
            info = str(df.loc[i, '生源地']).split('省')
            pri = info[0]
            df.loc[i, '全国各省'] = pri.split('市')[0]
            if '区' in str(df.loc[i, '全国各省']):
                df.loc[i, '全国各省'] = str(df.loc[i, '全国各省']).split('区')[0] + '区'
            try:
                city = info[1]
                df.loc[i, '广东各市'] = city.split('市')[0]
            except IndexError:
                city = info[0]
                df.loc[i, '广东各市'] = city
        df['全国各省'] = df['全国各省'].replace('nan', '')
        df['广东各市'] = df['广东各市'].replace('nan', '')
        df = df.drop(columns=['生源地','省份'])

        head = df.columns.tolist()
        app=[]

        for k in range(0, len(student_key)):  # 替换表头的名字 -- 将中文的表头变成英文的表头，固定死
            if head[k] == student_key[k]:
                pass
            else:
                head[k] = student_key[k]
        for h in head:
            app.append(student_data[h])
        df.columns = app

        print("解析成功！！！")
        df.to_csv(datapath.replace(".xlsx",".csv").replace(".xls",".csv"), index=False)

        df.to_sql("studentdata", con=engine, if_exists='append', index=False)
        return {'msg':"解析学生数据成功，记录条数:{}".format(df.count()),'code':0}
    except Exception as e:
        return {'msg':"解析学生数据失败:{}".format(traceback.format_exc()) , 'code':1}

@handle.route('/handleFile/characterData',methods=['POST'])
def parse_characterdata(datapath):
    
    df = pd.read_excel(datapath)
    head = df.columns.tolist()
    app = []

    for k in range(0, len(charact_key)):  # 替换表头的名字 -- 将中文的表头变成英文的表头，固定死
        if head[k] == charact_key[k]:
            pass
        else:
            head[k] = charact_key[k]
    for h in head:
        app.append(charact_data[h])
    df.columns = app
    try:
        print("解析成功！！！")
        df.to_csv(datapath.replace(".xlsx",".csv").replace(".xls",".csv"), index=False)

        df.to_sql("characterData", con=engine, if_exists='append', index=False)
        return {'msg':"解析学生数据成功，记录条数:{}".format(df.count()),'code':0}
    except Exception as e:
        return {'msg':"解析学生数据失败:{}".format(traceback.format_exc()) , 'code':1}

@handle.route('/handleFile/achievementData',methods=['POST'])
def parse_achievementdata(datapath):
    
    
    
    print("开始解析",datapath)
    df = pd.read_excel(datapath)
    head = df.columns.tolist()
    app = []

    for k in range(0, len(achievement_key)):  # 替换表头的名字 -- 将中文的表头变成英文的表头，固定死
        if head[k] == achievement_key[k]:
            pass
        else:
            head[k] = achievement_key[k]
    for h in head:
        app.append(achievement_data[h])
    df.columns = app
    try:
        print("解析成功！！！")
        df.to_csv(datapath.replace(".xlsx",".csv").replace(".xls",".csv"), index=False)

        df.to_sql("achievementdata", con=engine, if_exists='append', index=False)
        return {'msg':"解析学生数据成功，记录条数:{}".format(df.count()),'code':0}
    except Exception as e:
        return {'msg':"解析学生数据失败:{}".format(traceback.format_exc()) , 'code':1}

@handle.route('/handleFile/bookData',methods=['POST'])
def parse_bookdata(datapath):
    
    print("开始解析")
    df = pd.read_excel(datapath)
    head = df.columns.tolist()
    app = []

    for k in range(0, len(book_key)):  # 替换表头的名字 -- 将中文的表头变成英文的表头，固定死
        if head[k] == book_key[k]:
            pass
        else:
            head[k] = book_key[k]
    for h in head:
        app.append(book_data[h])
    df.columns = app
    try:
        print("解析成功！！！")
        df.to_csv(datapath.replace(".xlsx",".csv").replace(".xls",".csv"), index=False)
        print(df.head(5))
        df.to_sql("bookData", con=engine, if_exists='append', index=False)
        return {'msg':"解析学生数据成功，记录条数:{}".format(df.count()),'code':0}
    except Exception as e:
        return {'msg':"解析学生数据失败:{}".format(traceback.format_exc()) , 'code':1}









